package com.neusoft.neuiotms.dcam.fixed.repository;

import java.util.List;
import java.util.Map;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.neusoft.neuiotms.dcam.fixed.model.Asset;

public interface AssetRepository extends JpaRepository<Asset, Long>, CustomizedAssetRepository {
    long countByManagementUserCode(String username);

    long countByManagementUserCodeAndBelongsAndCategories(String username, String belongs, String categories);

    long countByManagementOrgCode(String orgCode);

    List<Asset> findByCodeIn(List<String> codes);

    List<Asset> findByIdIn(List<Long> idArr);

    Asset findByCode(String code);

    Asset findTopBySmallTypeCodeAndSpecificationsOrderByCreateTimeDesc(String smallCode, String specifications);

    Asset findTopBySmallTypeCodeAndSpecificationsAndManagementOrgCodeOrderByCreateTimeDesc(String smallCode,
            String specifications, String managementOrgCode);

    Asset findTopBySmallTypeCodeAndBelongsAndManagementOrgCodeOrderByCreateTimeDesc(String smallCode, String belongs,
            String managementOrgCode);

    long countByOriginalCodeAndBelongs(String cardNo, String belongs);

    List<Asset> findByOriginalCodeAndBelongs(String cardNo, String belongs);

    long countByCode(String code);

    List<Asset> findByCard(String card);

    /**
     * 资产总值
     *
     * @param belongs
     * @param categories
     * @return
     */
    @Query(value = "SELECT SUM(money) FROM neuiot_fixed_asset WHERE belongs = :belongs and categories in (:categories)", nativeQuery = true)
    Double totalMoney(@Param("belongs") String belongs, @Param("categories") List<String> categories);

    /**
     * 按资产大类统计
     *
     * @param belongs
     * @return
     */
    @Query(nativeQuery = true, value = "select big_type_name as name, count(*) as count, sum(money) as amount from neuiot_fixed_asset where belongs = :belongs and categories in (1,2) and big_type_name is not null GROUP BY big_type_name ORDER BY count desc")
    List<Map<String, Object>> statsByBigType(@Param("belongs") String belongs);

    /**
     * 按资产大类统计状态、金额和数量
     *
     * @param belongs
     * @return
     */
    @Query(nativeQuery = true, value = "SELECT categories, COUNT( CASE WHEN STATUS = 'used' THEN 1 ELSE NULL END ) AS 'syCount', COUNT( CASE WHEN STATUS = 'idle' THEN 1 ELSE NULL END ) AS 'xzCount', COUNT( CASE WHEN STATUS = 'unused' THEN 1 ELSE NULL END ) AS 'kcCount', COUNT( CASE WHEN STATUS = 'toScrap' THEN 1 ELSE NULL END ) AS 'dbfCount', COUNT( CASE WHEN STATUS = 'scrap' THEN 1 ELSE NULL END ) AS 'scrapCount', COUNT( CASE WHEN STATUS = 'transfer' THEN 1 ELSE NULL END ) AS 'transferCount', COUNT( CASE WHEN STATUS = 'unmaintenance' THEN 1 ELSE NULL END ) AS 'unmaintenanceCount', COUNT( CASE WHEN STATUS = 'borrow' THEN 1 ELSE NULL END ) AS 'borrowCount' FROM neuiot_fixed_asset WHERE belongs = :belongs AND categories IN (1, 2,3) AND STATUS IS NOT NULL GROUP BY categories ORDER BY categories")
    List<Map<String, Object>> statsByBigTypeAndStatus(@Param("belongs") String belongs);

    /**
     * 按资产中类统计状态、金额和数量
     *
     * @param belongs
     * @return
     */
    @Query(nativeQuery = true, value = "select count(*) as count, truncate(sum(money) / 10000,2) AS amount, middle_type_name AS middleTypeName, big_type_name As bigTypeName from neuiot_fixed_asset where belongs = :belongs and categories in (1,2,3) and middle_type_name is not null and status is not null GROUP BY middle_type_name, big_type_name ORDER BY big_type_name DESC")
    List<Map<String, Object>> statsByMiddleTypeAndStatus(@Param("belongs") String belongs);

    /**
     * 按资产中类统计
     *
     * @param belongs
     * @return
     */
    @Query(nativeQuery = true, value = "select middle_type_name as name, count(*) as count, sum(money) as amount from neuiot_fixed_asset where belongs = :belongs and categories in (1,2) and middle_type_name is not null GROUP BY middle_type_name ORDER BY count desc")
    List<Map<String, Object>> statsByMiddleType(@Param("belongs") String belongs);

    /**
     * 按资产中类统计
     *
     * @param belongs
     * @return
     */
    @Query(nativeQuery = true, value = "select middle_type_name as name, count(*) as count, sum(money) as amount from neuiot_fixed_asset where belongs = :belongs and categories in (1,2) and middle_type_name is not null and big_type_name = :bigTypeName GROUP BY middle_type_name ORDER BY count desc")
    List<Map<String, Object>> statsByMiddleTypeAndBigType(@Param("belongs") String belongs,
            @Param("bigTypeName") String bigTypeName);

    /**
     * 按使用部门和分类统计资产
     *
     * @param belongs
     * @return
     */
    @Query(nativeQuery = true, value = "SELECT management_org_name AS name, COUNT( CASE WHEN categories = 1 THEN 1 ELSE NULL END ) AS 'gdCount', COUNT( CASE WHEN categories = 2 THEN 1 ELSE NULL END ) AS 'dzyhCount', COUNT( CASE WHEN categories = 3 THEN 1 ELSE NULL END ) AS 'kxpCount', COUNT( CASE WHEN categories in (1,2,3) THEN 1 ELSE NULL END ) AS 'count' FROM neuiot_fixed_asset WHERE belongs = :belongs AND management_org_name IS NOT NULL GROUP BY management_org_name ORDER BY count DESC")
    List<Map<String, Object>> statsByOgAndCategories(@Param("belongs") String belongs);

    /**
     * 按使用个人和分类统计资产
     *
     * @param belongs
     * @return
     */
    @Query(nativeQuery = true, value = "SELECT management_user_name AS name, COUNT( CASE WHEN categories = 1 THEN 1 ELSE NULL END ) AS 'gdCount', COUNT( CASE WHEN categories = 2 THEN 1 ELSE NULL END ) AS 'dzyhCount', COUNT( CASE WHEN categories = 3 THEN 1 ELSE NULL END ) AS 'kxpCount', COUNT( CASE WHEN categories in (1,2,3) THEN 1 ELSE NULL END ) AS 'count' FROM neuiot_fixed_asset WHERE belongs = :belongs AND management_user_name IS NOT NULL GROUP BY management_user_name ORDER BY count DESC")
    List<Map<String, Object>> statsByUserAndCategories(@Param("belongs") String belongs);

    /**
     * 按使用部门统计
     *
     * @param belongs
     * @return
     */
    @Query(nativeQuery = true, value = "select management_org_name as name, count(*) as count, sum(money) as amount from neuiot_fixed_asset where belongs = :belongs and categories in (1,2) and management_org_name is not null GROUP BY management_org_name ORDER BY count desc")
    List<Map<String, Object>> statsByOg(@Param("belongs") String belongs);

    /**
     * 按状态统计
     *
     * @param belongs
     * @return
     */
    @Query(nativeQuery = true, value = "select t2.text as name, t1.count, t1.amount from (select status as name, count(*) as count, sum(money) as amount from neuiot_fixed_asset where belongs = :belongs and categories in (1,2) and status is not null GROUP BY status ORDER BY count desc) t1 left join neuiot_dictionary t2 on t1.name = t2.code order by t1.count desc")
    List<Map<String, Object>> statsByStatus(@Param("belongs") String belongs);

    /**
     * 按库存分类统计
     *
     * @param belongs
     * @return
     */
    @Query(nativeQuery = true, value = "select t1.small_type_name as name, count(*) as count from neuiot_fixed_asset t1 left join (select a.name, b.code as location_code from neuiot_fixed_storehouse a left join neuiot_location b on a.location_id = b.id) t2 on t1.location = t2.location_code where t1.location in (:locations) and t1.status = 'unused' and  t2.location_code is not null group by t1.small_type_name")
    List<Map<String, Object>> statsByStorehouse(@Param("locations") List<String> locations);

}
